﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.SqlClient;
using System.Data;
public partial class EAReject : System.Web.UI.Page
{

    protected string connectstring = System.Configuration.ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ConnectionString;
    public static String date = DateTime.Now.Date.ToString("yyyy-MM-dd");
    public static String time = DateTime.Now.ToString("yyyyMMdd_HHmmss");
    public static String CancsPre = "SSE_TO_EA_Cancs_Fix_";
    public static String SalesPre = "SSE_TO_EA_Sales_Fix_";
    public static String CancsFilename = CancsPre + time + ".txt";
    public static String SalesFilename = SalesPre + time + ".txt";
    protected void Page_Load(object sender, EventArgs e)
    {
        ((Label)Header.FindControl("lblUser")).Text = (String)Session["UserName"];
        
    }

    protected void Button5_Click(object sender, EventArgs e)
    {
        MultiView1.ActiveViewIndex = 1;
        DetailsView2.DataSourceID = "SqlDataSource3";
    }
    protected void Button6_Click(object sender, EventArgs e)
    {
        MultiView1.ActiveViewIndex = 2;
        DetailsView2.DataSourceID = "SqlDataSource4";
    }

    protected void insertCanceRejects(string CHANNEL, string LOAD_NO, string FILE_NAME, string FILE_LINE_NO, string VERBAL_ACCEPTANCE_NO, string VALIDATION_TYPE, string COMMENTS)
    {
        string Commond = "insert into tblEACancelReject Values('" + CHANNEL + "','" + LOAD_NO + "','" + FILE_NAME + "','" + FILE_LINE_NO + "','" + VERBAL_ACCEPTANCE_NO + "','" + VALIDATION_TYPE + "','" + COMMENTS + "');";
        string connectionString = null;
        System.Configuration.ConnectionStringSettings connString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"];
        if (null != connString)
            connectionString = connString.ConnectionString;
        System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); ;
        connection.Open();
        System.Data.SqlClient.SqlCommand command;
        command = connection.CreateCommand();
        command.CommandText = Commond;
        command.ExecuteNonQuery();
        connection.Close();
    }

    protected void insertSalesRejects(string CHANNEL, string LOAD_NO, string FILE_NAME, string FILE_LINE_NO, string VERBAL_ACCEPTANCE_NO, string VALIDATION_TYPE, string COMMENTS, string CANCELLATION_DATE, string CUSTOMER_TYPE, string DATE_ACCEPTED, string DOCUMENT_NUMBER, string SITE_STATE, string AGEING_FROM_INITIAL_LOAD)
    {
        string Commond = "insert into tblEAReject Values('" + CHANNEL + "','" + LOAD_NO + "','" + FILE_NAME + "','" + FILE_LINE_NO + "','" + VERBAL_ACCEPTANCE_NO + "','" + VALIDATION_TYPE + "','" + COMMENTS + "','" + CANCELLATION_DATE + "','" + CUSTOMER_TYPE + "','" + DATE_ACCEPTED + "','" + DOCUMENT_NUMBER + "','" + SITE_STATE + "','" + AGEING_FROM_INITIAL_LOAD + "');";
        string connectionString = null;
        System.Configuration.ConnectionStringSettings connString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"];
        if (null != connString)
            connectionString = connString.ConnectionString;
        System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); ;
        connection.Open();
        System.Data.SqlClient.SqlCommand command;
        command = connection.CreateCommand();
        command.CommandText = Commond;
        command.ExecuteNonQuery();
        connection.Close();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        DirectoryInfo folder = new DirectoryInfo(Server.MapPath("."));
        foreach (FileInfo file in folder.GetFiles("*.txt"))
        {
            if (file.FullName.Contains("EA_TO_SSE_Cancs_Rejects"))
            {
                using (StreamReader sr = new StreamReader(file.FullName))
                {
                    String line;
                    sr.ReadLine();
                    while ((line = sr.ReadLine()) != null)
                    {
                        string[] sArray = line.Split('|');
                        insertCanceRejects(sArray[0], sArray[1], sArray[2], sArray[3], sArray[4], sArray[5], sArray[6]);
                    }
                }
            }
        }
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        DirectoryInfo folder = new DirectoryInfo(Server.MapPath("."));
        foreach (FileInfo file in folder.GetFiles("*.txt"))
        {
            if (file.FullName.Contains("EA_TO_SSE_Sales_Rejects"))
            {
                using (StreamReader sr = new StreamReader(file.FullName))
                {
                    String line;
                    sr.ReadLine();
                    while ((line = sr.ReadLine()) != null)
                    {
                        string[] sArray = line.Split('|');
                        insertSalesRejects(sArray[0], sArray[1], sArray[2], sArray[3], sArray[4], sArray[5], sArray[6], dateformat(sArray[7]), sArray[8], dateformat(sArray[9]), sArray[10], sArray[11], sArray[12]);
                    }
                }
            }
        }

    }

    protected string dateformat(string olddate)
    {
        if (olddate.Equals(""))
            return olddate;
        else
        {
            string day = olddate.Substring(0, 2);
            string month = olddate.Substring(3, 2);
            string year = olddate.Substring(6, 4);
            string newdate = year + "-" + month + "-" + day;
            return newdate;
        }
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        String querySql = "select distinct a.VERBAL_ACCEPTANCE_NO,CANCELLATION_DATE,CANCELLED_BY,CANCELLED_REASON from tblEACancel a,tblEACancelReject b where a.VERBAL_ACCEPTANCE_NO=b.VERBAL_ACCEPTANCE_NO;";
        SqlConnection SqlConnection = new SqlConnection();
        SqlConnection.ConnectionString = connectstring;
        SqlCommand command = new SqlCommand(querySql, SqlConnection);
        SqlConnection.Open();
        DataTable dt = GetDataTable(command);
        //ExportOnline(dt);
        ExportCancs(dt);
        SqlConnection.Close();

    }
    protected void Button4_Click(object sender, EventArgs e)
    {
        String querySql = "select distinct [ID], [UserID], [CAMPAINGN_NUMBER], a.[VERBAL_ACCEPTANCE_NO], a.[DATE_ACCEPTED], [CALL_TYPE], [FUEL_TYPE], a.[CUSTOMER_TYPE], [NETWORK_AREA], [OFFER_SELECTION], [PRODUCT_INCENTIVE], [UPFRONT_DISCOUNT], [OFFER_PARTNER], [COMPANY_NAME], [TRADING_NAME], [COMPANY_POSITION], [ABN], [TITLE], [FIRST_NAME], [LAST_NAME], [BILL_NAME_1], [BILL_NAME_2], [DOB], [DRIVERS_LICENCE], [HPHONE], [BPHONE], [MOBILE], [EMAIL], [TITLE_CUST_2], [FIRST_NAME_CUST_2], [LAST_NAME_CUST_2], [DOB_CUST_2], [DRIVERS_LICENCE_CUST_2], [HPHONE_CUST_2], [BPHONE_CUST_2], [MOBILE_CUST_2], [EMAIL_CUST_2], [RELATIONSHIP_CODE], [DO_NOT_CONTACT], [SITE_ADD1], [SITE_SUBURB], a.[SITE_STATE], [SITE_POSTCODE], [POST_ADDRESS_1], [POST_ADDRESS_2], [POST_ADDRESS_3], [POST_ADDRESS_4], [POST_SUBURB], [POST_STATE], [POST_POSTCODE], [ACCOUNT_ID], [NMI], [DPI], [CURRENT_ELECTRICITY_RETAILER], [OPERATOR_NAME], [ANNUAL_CONSUMPTION], [OFFER_STATUS], [AFFILIATION_INITIAL], [AFFILIATION_SURNAME], [AFFILIATION_MEMBER_NO], [PAYMENT_OPTION], [SMARTER_PAY_CREDIT], [RPO_FIRST_PAYMENT_DATE], [RPO_ELEC_MONTH], [RPO_ELEC_FORTN], [RPO_GAS_MONTH], [RPO_GAS_FORTN], [RPO_COMB_MONTH], [RPO_COMB_FORTN], [RPO_FREQ_SELECTION], [DD_DETAILS_GIVEN], [PAY_SELECTION], [NAME_FIN_INST], [ACC_HOLDER_NAME], [BSB], [ACCOUNT_NUMBER], [CARD_TYPE], [CARD_NUMBER], [VERIF_NUMBER], [CARD_NAME], [EXPIRY_DATE], [PAYMENT_OPTION_PRIOR_TO_SALE], [SELL_CODE], [VERIFICATION_OPERATOR_COMMENTS], a.[DOCUMENT_NUMBER], [D2D_REP_NAME_ID], [D2D_AGENCY], [DISCOUNT_PERCENT], [MKT_GREEN_RATIO], [DPID], [LIFE_SUPPORT] from tblEA a, tblEAReject b where a.VERBAL_ACCEPTANCE_NO=b.VERBAL_ACCEPTANCE_NO;";
        SqlConnection SqlConnection = new SqlConnection();
        SqlConnection.ConnectionString = connectstring;
        SqlCommand command = new SqlCommand(querySql, SqlConnection);
        SqlConnection.Open();
        DataTable dt = GetDataTable(command);
        //ExportOnline(dt);
        ExportSales(dt);
        SqlConnection.Close();
    }
    public static DataTable GetDataTable(System.Data.SqlClient.SqlCommand cmd)
    {

        // create a new data adapter based on the specified query.
        SqlDataAdapter da = new SqlDataAdapter();
        //set the SelectCommand of the adapter
        da.SelectCommand = cmd;
        // create a new DataTable
        DataTable dtGet = new DataTable();
        //fill the DataTable
        da.Fill(dtGet);
        //return the DataTable
        return dtGet;
    }

    public void ExportCancs(DataTable dt)
    {
        string path = Server.MapPath(".");
        string filepath = path + "\\" + CancsFilename;
        StreamWriter w = File.CreateText(@filepath);
        try
        {
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                w.Write(dt.Columns[i]);
                if (i < dt.Columns.Count - 1)
                    w.Write('|');
            }
            w.Write('\n');
            object[] values = new object[dt.Columns.Count];
            for (int j = 0; j < dt.Rows.Count; j++)
            //foreach (System.Data.DataRow dr in dt.Rows)
            {
                DataRow dr = dt.Rows[j];
                values = dr.ItemArray;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (i == 1)
                    {
                        if (values[i].ToString() != "")
                            w.Write(ChangeDateFormit(values[i].ToString()));
                        else
                            w.Write(values[i]);
                    }
                    else
                        w.Write(values[i]);
                    if (i < dt.Columns.Count - 1)
                        w.Write('|');
                }
                if (j < dt.Rows.Count - 1)
                    w.Write('\n');
            }
            w.Flush();
            w.Close();
        }
        catch
        {
            w.Close();
        }
    }

    public void ExportSales(DataTable dt)
    {
        string path = Server.MapPath(".");
        string filepath = path + "\\" + SalesFilename;
        StreamWriter w = File.CreateText(@filepath);
        try
        {
            for (int i = 2; i < dt.Columns.Count; i++)
            {
                w.Write(dt.Columns[i]);
                if (i < dt.Columns.Count - 1)
                    w.Write('|');
            }
            w.Write('\n');
            object[] values = new object[dt.Columns.Count];
            for (int j = 0; j < dt.Rows.Count; j++)
            //foreach (System.Data.DataRow dr in dt.Rows)
            {
                DataRow dr = dt.Rows[j];
                values = dr.ItemArray;
                for (int i = 2; i < dt.Columns.Count; i++)
                {
                    if (i == 4 || i == 22 || i == 31 || i == 62)
                    {
                        if (values[i].ToString() != "")
                            w.Write(ChangeDateFormit(values[i].ToString()));
                        else
                            w.Write(values[i]);
                    }
                    else
                        w.Write(values[i]);
                    if (i < dt.Columns.Count - 1)
                        w.Write('|');
                }
                if (j < dt.Rows.Count - 1)
                    w.Write('\n');
            }
            w.Flush();
            w.Close();
        }
        catch
        {
            w.Close();
        }
    }

    public string ChangeDateFormit(string olddate)
    {
        DateTime date = Convert.ToDateTime(olddate);
        string year = date.Year.ToString();
        string month = "";
        string day = "";
        if (date.Month.ToString().Length < 2)
            month = "0" + date.Month.ToString();
        else
            month = date.Month.ToString();
        if (date.Day.ToString().Length < 2)
            day = "0" + date.Day.ToString();
        else
            day = date.Day.ToString();
        string newdate = day + "/" + month + "/" + year;
        return newdate;
    }
}